﻿using DataAchive.Model.DBModels;
using DataAchive.Model.ViewModels;
using SoftCommon;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;

namespace DataAchive.Helper
{
    public class NQueryBuilder : NDbHelperSqlite
    {
        public NQueryBuilder()
        {

        }
        public NQueryBuilder(string dbpath)
        {

        }
        #region 数据库操作
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public T GetModel<T>(int ID)
        {
            Type objType = typeof(T);
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            StringBuilder strSql = new StringBuilder();
            strSql.Append($"SELECT * FROM {TableName} ");
            strSql.Append(" WHERE ID=@ID ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@ID", DbType.Int32,8)};
            parameters[0].Value = ID;

            DataSet ds = Query(strSql.ToString(), parameters);


            List<T> list = DataSetToList<T>(ds);
            return list[0];
        }

        public List<T> GteFittleList<T>(T t)
        {
            Type objType = typeof(T);
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> whereSql = new List<string>();


            foreach (var item in propertys)
            {
                object value = item.GetValue(t);

                if (value != null && value != DBNull.Value)
                {
                    ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                    if (columnAttribute != null)
                    {
                        string columName = columnAttribute.Name;
                        whereSql.Add($" {columName}=@{item.Name} ");
                        parameters.Add(new SQLiteParameter($"@{item.Name}", item.GetValue(t)));
                    }
                }
            }

            string sql = $"SELECT * FROM {TableName}";
            if (parameters.Count > 0)
            {
                sql += $" WHERE {string.Join(" AND ", whereSql)}";
            }
            DataSet ds = Query(sql.ToString(), parameters.ToArray());
            List<T> list = DataSetToList<T>(ds);
            return list;
        }


        public PageModel<T> GetPageModelList<T>(T t, List<FilterModel> filterModel = null, int? PageIndex = null, int? PageSize = null)
        {
            Type objType = typeof(T);
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> whereSql = new List<string>();


            foreach (var item in propertys)
            {
                object value = item.GetValue(t);

                if (value != null && value != DBNull.Value)
                {
                    ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                    if (columnAttribute != null)
                    {
                        string columName = columnAttribute.Name;
                        whereSql.Add($" {columName}=@{item.Name} ");
                        parameters.Add(new SQLiteParameter($"@{item.Name}", item.GetValue(t)));
                    }
                }
            }


            List<string> otherSlq = new List<string>();
            if (filterModel != null && filterModel.Any())
            {
                for (int i = 0; i < filterModel.Count; i++)
                {
                    otherSlq.Add(GetFilter(filterModel[i], i, ref parameters));
                }
            }


            string sql = $"SELECT * FROM {TableName}";
            string filter = "";
            if (parameters.Count > 0)
            {
                filter += $" WHERE ";
                if (whereSql != null && whereSql.Any())
                {
                    filter += $"{string.Join(" AND ", whereSql)} ";
                }
                if (otherSlq != null && otherSlq.Any())
                {
                    filter += $"{string.Join(" AND ", otherSlq)} ";
                }

            }


            int count = 0;
            if (PageSize != null && PageIndex != null)
            {
                string sqlCount = $"SELECT COUNT(*) FROM {TableName}{filter}";
                count = Convert.ToInt32(GetSingle(sqlCount, parameters.ToArray()));

                filter += GetPage(PageIndex ?? 1, PageSize ?? 10);

            }
            DataSet ds = Query(sql.ToString() + filter, parameters.ToArray());
            List<T> list = DataSetToList<T>(ds);
            PageModel<T> listPage = new PageModel<T>()
            {
                Count = count,
                PageIndex = PageIndex ?? 0,
                PageSize = PageSize ?? 0,
                Data = list
            };
            return listPage;
        }

        /// <summary>
        /// 获取对象List
        /// </summary>
        public List<T> GetModelList<T>(T t, List<FilterModel> filterModel = null)
        {
            Type objType = typeof(T);
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> whereSql = new List<string>();


            foreach (var item in propertys)
            {
                object value = item.GetValue(t);

                if (value != null && value != DBNull.Value)
                {
                    ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                    if (columnAttribute != null)
                    {
                        string columName = columnAttribute.Name;
                        whereSql.Add($" {columName}=@{item.Name} ");
                        parameters.Add(new SQLiteParameter($"@{item.Name}", item.GetValue(t)));
                    }
                }
            }


            List<string> otherSlq = new List<string>();
            if (filterModel != null && filterModel.Any())
            {
                for (int i = 0; i < filterModel.Count; i++)
                {
                    otherSlq.Add(GetFilter(filterModel[i], i, ref parameters));
                }
            }


            string sql = $"SELECT * FROM {TableName}";
            if (parameters.Count > 0)
            {
                sql += $" WHERE ";
                if (whereSql != null && whereSql.Any())
                {
                    sql += $"{string.Join(" AND ", whereSql)} ";
                }
                if (otherSlq != null && otherSlq.Any())
                {
                    if (whereSql != null && whereSql.Any())
                        sql += "AND";
                    sql += $"{string.Join(" AND ", otherSlq)} ";
                }

            }
            DataSet ds = Query(sql.ToString(), parameters.ToArray());
            List<T> list = DataSetToList<T>(ds);
            return list;
        }


        public List<T> QuerySql<T>(string sql, SQLiteParameter[] sQLiteParameters)
        {
            try
            {
                var list = Query(sql, sQLiteParameters);
                if (list == null || list.Tables.Count <= 0)
                {
                    return new List<T>();
                }
                return DataSetToList<T>(list);
            }
            catch (Exception ex)
            {
                Log.Write(ex); 
                return new List<T>();
            }
        }


        /// <summary> 
        /// DataSetToList 
        /// </summary> 
        /// <typeparam name="T">转换类型</typeparam> 
        /// <param name="dataSet">数据源</param> 
        /// <param name="tableIndex">需要转换表的索引</param> 
        /// <returns>泛型集合</returns> 
        public List<T> DataSetToList<T>(DataSet dataSet)
        {
            DataTable dt = dataSet.Tables[0];

            List<T> list = new List<T>();

            if (typeof(T) == typeof(int) || typeof(T) == typeof(string)||typeof(T)==typeof(double)||typeof(T)==typeof(bool))
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][0] != DBNull.Value)
                    {
                        T item = (T)Convert.ChangeType(dt.Rows[i][0], typeof(T));
                        list.Add(item);
                    }
                }
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //创建泛型对象 
                    T _t = Activator.CreateInstance<T>();

                    //获取对象所有属性 
                    PropertyInfo[] propertyInfo = _t.GetType().GetProperties();

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        foreach (PropertyInfo info in propertyInfo)
                        {
                            ColumnAttribute columnAttribute = info.GetCustomAttribute<ColumnAttribute>();
                            if (columnAttribute != null)
                            {
                                string columName = columnAttribute.Name;
                                //属性名称和列名相同时赋值 
                                if (dt.Columns[j].ColumnName.ToUpper().Equals(columnAttribute.Name.ToUpper()))
                                {
                                    if (dt.Rows[i][j] != DBNull.Value)
                                    {
                                        info.SetValue(_t, dt.Rows[i][j], null);
                                    }
                                    else
                                    {
                                        info.SetValue(_t, null, null);
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    list.Add(_t);
                }
            }

   
            return list;
        }



        /// <summary>
        /// 实体新增 返回新增ID
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Insert<T>(T t)
        {
            try
            {
                var objType = t.GetType();
                string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                    .ConstructorArguments.FirstOrDefault().Value.ToString();
                var propertys = objType.GetProperties();
                //var data = propertys.ToDictionary(p => p.Name, p => p.GetValue(t, null));
                List<SQLiteParameter> parameters = new List<SQLiteParameter>();
                List<string> addSql = new List<string>();
                List<string> columSql = new List<string>();
                foreach (var item in propertys)
                {
                    bool isKey = item.CustomAttributes.Any(s => s.AttributeType.Name == nameof(KeyAttribute));
                    if (!isKey)
                    {
                        ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                        if (columnAttribute != null)
                        {
                            string columName = columnAttribute.Name;
                            columSql.Add(columName);
                            addSql.Add($"@{item.Name}");
                            parameters.Add(new SQLiteParameter($"@{item.Name}", item.GetValue(t)));
                        }

                    }
                }
                string sql = $" INSERT INTO {TableName} ({string.Join(",", columSql)})  VALUES ({string.Join(",", addSql)})";
                int rows = InsertGetId(sql, parameters.ToArray());
                return rows;

            }
            catch (Exception ex)
            {
                SoftCommon.Log.Write(SoftCommon.LogType.Error, $"插入失败：{ex.Message}");
                return 0;
            }
        }

        /// <summary>
        /// 实体新增 返回新增ID
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public void InsertMany<T>(List<T> t)
        {
            if (t == null)
            {
                SoftCommon.Log.Write(SoftCommon.LogType.Warnning, "传入对象为空！");
                return;
            }
            var objType = typeof(T);
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            //var data = propertys.ToDictionary(p => p.Name, p => p.GetValue(t, null));
            List<KeyValuePair<string, SQLiteParameter[]>> list = new List<KeyValuePair<string, SQLiteParameter[]>>();
            t.ForEach(model =>
            {

                List<SQLiteParameter> parameters = new List<SQLiteParameter>();
                List<string> addSql = new List<string>();
                List<string> columSql = new List<string>();
                foreach (var item in propertys)
                {
                    bool isKey = item.CustomAttributes.Any(s => s.AttributeType.Name == nameof(KeyAttribute));
                    if (!isKey)
                    {

                        ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                        if (columnAttribute != null)
                        {
                            string columName = columnAttribute.Name;
                            columSql.Add(columName);
                            addSql.Add($"@{item.Name}");
                            parameters.Add(new SQLiteParameter($"@{item.Name}", item.GetValue(model)));
                        }
                    }
                }
                string sql = $@" INSERT INTO {TableName} ({string.Join(",", columSql)})  
                        VALUES ({string.Join(",", addSql)});";
                list.Add(new KeyValuePair<string, SQLiteParameter[]>(sql, parameters.ToArray()));

            });


            ExecuteNonQueryBatch(list);
        }



        /// <summary>
        /// 实体修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Update<T>(T t)
        {
            var objType = t.GetType();
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            //var data = propertys.ToDictionary(p => p.Name, p => p.GetValue(t, null));
            List<string> strSql = new List<string>();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            List<string> whereSql = new List<string>();
            foreach (var item in propertys)
            {
                object value = item.GetValue(t);
                bool isKey = item.CustomAttributes.Any(s => s.AttributeType.Name == nameof(KeyAttribute));
                ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                if (columnAttribute != null)
                {
                    string columName = columnAttribute.Name;
                    if (!isKey)
                        strSql.Add($" {columName}=@{item.Name} ");
                    else
                        whereSql.Add($" {columName}=@{item.Name} ");
                    parameters.Add(new SQLiteParameter($"@{item.Name}", value));
                }
            }
            string sql = $" UPDATE {TableName} SET {string.Join(",", strSql)} WHERE {string.Join(",", whereSql)}";
            int rows = ExecuteSql(sql, parameters.ToArray());
            return rows;
        }

        /// <summary>
        /// 根据表名+主键删除表数据
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool Delete(string TableName, int ID)
        {
            SQLiteParameter[] parameters = { new SQLiteParameter("@ID", ID) };
            string sql = $"DELETE FROM {TableName} WHERE ID=@ID";
            int rows = ExecuteSql(sql, parameters);
            if (rows > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 根据实体主键删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Delete<T>(T t)
        {
            var objType = t.GetType();
            string TableName = objType.CustomAttributes.FirstOrDefault(f => f.AttributeType.Name == nameof(TableAttribute))
                .ConstructorArguments.FirstOrDefault().Value.ToString();
            var propertys = objType.GetProperties();
            //var data = propertys.ToDictionary(p => p.Name, p => p.GetValue(t, null));
            List<string> strSql = new List<string>();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            foreach (var item in propertys)
            {
                object value = item.GetValue(t);
                bool isKey = item.CustomAttributes.Any(s => s.AttributeType.Name == nameof(KeyAttribute));
                ColumnAttribute columnAttribute = item.GetCustomAttribute<ColumnAttribute>();
                if (columnAttribute != null)
                {
                    string columName = columnAttribute.Name;
                    if (isKey)
                        strSql.Add($" {columName}=@{item.Name} ");
                    parameters.Add(new SQLiteParameter($"@{item.Name}", value));
                }
            }
            string sql = $"DELETE FROM {TableName} WHERE  {string.Join(",", strSql)}";
            int rows = ExecuteSql(sql, parameters.ToArray());
            return rows > 0;
        }

        #region sql条件
        public string GetFilter(FilterModel filterModel, int index, ref List<SQLiteParameter> sQLiteParameters)
        {

            switch (filterModel.Type)
            {
                case FilterType.None:
                    return "";
                case FilterType.Like:
                    return Like(filterModel.Name, index, filterModel.Like, ref sQLiteParameters);
                case FilterType.In:
                    return In(filterModel.Name, index, filterModel.In, ref sQLiteParameters);
                case FilterType.Great:
                    return GreatThen(filterModel.Name, index, filterModel.Great, ref sQLiteParameters);
                case FilterType.Less:
                    return LessThen(filterModel.Name, index, filterModel.Less, ref sQLiteParameters);
                case FilterType.Equal:
                    return " ";
                case FilterType.GreatOrEqual:
                    return GreatThenOrEqual(filterModel.Name, index, filterModel.GreatOrEqual, ref sQLiteParameters);
                case FilterType.LessOrEqual:
                    return LessThen(filterModel.Name, index, filterModel.LessOrEqual, ref sQLiteParameters);
                case FilterType.BetweenEnd:
                    return BetweenEnd(filterModel.Name, index, filterModel.BetweenStart, filterModel.BetweenEnd, ref sQLiteParameters);
                default:
                    return "";
            }

        }


        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        /// 
        public string GetPage(int PageIndex, int PageSize)
        {
            return $" LIMIT {PageSize} OFFSET {(PageIndex - 1) * PageSize}";
        }


        public enum OrderByType
        {
            ASC,
            DESC
        }
        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="name"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public string GetOrderBy(string name, OrderByType orderBy)
        {
            switch (orderBy)
            {
                case OrderByType.ASC:
                    return $" OrderBy {name} ASC ";
                case OrderByType.DESC:
                    return $" OrderBy {name} DESC ";
                default:
                    return "";
            }
        }


        public string In(string key, int index, object[] arrs, ref List<SQLiteParameter> parameters)
        {
            List<string> strings = new List<string>();
            for (int i = 0; i < arrs.Length; i++)
            {
                strings.Add($"@InParam{index}_{i}");
                parameters.Add(new SQLiteParameter($"@InParam{index}_{i}", arrs[i]));
            }
            return $" {key} IN ({string.Join(",", strings)}) ";
        }

        public string Like(string key, int index, string likeStr, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@LikeParam{index}", $"%{likeStr}%"));
            return $" {key} LIKE @LikeParam{index} ";
        }

        public string GreatThen(string key, int index, object obj, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@GreatThenParam{index}", obj));
            return $" {key} > %@GreatThenParam{index}% ";
        }
        public string GreatThenOrEqual(string key, int index, object obj, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@GreatThengOrEqualParam{index}", obj));
            return $" {key} >= %@GreatThengOrEqualParam{index}% ";
        }
        public string LessThen(string key, int index, object obj, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@LessThenParam{index}", obj));
            return $" {key} < %@LessThenParam{index}% ";
        }
        public string LessThenOrEqual(string key, int index, object obj, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@LessThenOrEqualParam{index}", obj));
            return $" {key} <= '@LessThenOrEqualParam{index}% ";
        }

        public string BetweenEnd(string key, int index, object start, object end, ref List<SQLiteParameter> parameters)
        {
            parameters.Add(new SQLiteParameter($"@BetweenStartParam{index}", start));
            parameters.Add(new SQLiteParameter($"@BetweenEndParam{index}", end));
            return $" {key} BETWEEN @BetweenStartParam{index} AND @BetweenEndParam{index} ";
        }


        #endregion



        #endregion


        #region SQLHelper

        #region 公用方法

        public override int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public override bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public override bool Exists(string strSql, params SQLiteParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion

        #region  执行简单SQL语句


        /// <summary>
        /// 插入一条sql，返回id
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public override int InsertGetId(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        // 获取最后插入的行的 ID
                        cmd.CommandText = "SELECT last_insert_rowid()";
                        int newId = Convert.ToInt32(cmd.ExecuteScalar());
                        return newId;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        connection.Close();
                        SoftCommon.Log.Write($"SQL异常：{E.Message}");
                        return 0;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public override int ExecuteSql(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        connection.Close();
                        SoftCommon.Log.Write($"SQL异常：{E.Message}");
                        return -1;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        public override void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    tx.Rollback();
                    SoftCommon.Log.Write($"SQL异常：{E.Message}");
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public override int ExecuteSql(string SQLString, string content)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
                SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    SoftCommon.Log.Write($"SQL异常：{E.Message}");
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public override int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
                SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    SoftCommon.Log.Write($"SQL异常：{E.Message}");
                    return 0;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        connection.Close();
                        SoftCommon.Log.Write($"SQL异常：{e.Message}");
                        return default;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public override SQLiteDataReader ExecuteReader(string strSQL)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            try
            {
                connection.Open();
                SQLiteDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SQLite.SQLiteException e)
            {
                SoftCommon.Log.Write($"SQL异常：{e.Message}");
                return null;
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public override DataSet Query(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                }
                return ds;
            }
        }


        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行SQL语句，返回新增ID
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public override int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;

                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        SoftCommon.Log.Write($"SQL异常：{E.Message}");
                        return 0;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public override int InsertGetId(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        // 获取最后插入的行的 ID
                        cmd.CommandText = "SELECT last_insert_rowid()";
                        int newId = Convert.ToInt32(cmd.ExecuteScalar());
                        return newId;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        SoftCommon.Log.Write($"SQL异常：{E.Message}");
                        return 0;
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
        public override void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteTransaction trans = conn.BeginTransaction())
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        trans.Rollback();

                        SoftCommon.Log.Write($"SQL异常,事务回滚：{E.Message}");
                    }
                }
            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        SoftCommon.Log.Write($"SQL异常：{e.Message}");
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public override SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SQLiteDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SQLite.SQLiteException e)
            {
                SoftCommon.Log.Write($"SQL异常：{e.Message}");
                return null;
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public override DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SQLite.SQLiteException ex)
                    {
                        SoftCommon.Log.Write($"SQL异常：{ex.Message}");
                    }
                    return ds;
                }
            }
        }


        private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion

        /// <summary>
        /// 批量处理数据操作语句。
        /// </summary>
        /// <param name="list">SQL语句集合。</param>
        /// <exception cref="Exception"></exception>
        public override void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                try { conn.Open(); }
                catch { return; }
                using (SQLiteTransaction tran = conn.BeginTransaction())
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        try
                        {
                            foreach (var item in list)
                            {
                                cmd.CommandText = item.Key;
                                if (item.Value != null)
                                {
                                    cmd.Parameters.AddRange(item.Value);
                                }
                                cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            SoftCommon.Log.Write($"SQL异常,事务回滚：{ex.Message}");
                        }
                    }
                }
            }
        }
        #endregion
    }
}
